clear all
cap log close

log using "${logfiles}Summary Statistics.log", replace


// The codes comment out here are used for generate the intermediate input data. Users should run them the first time and then leave them comment out. 

/*
// prepare data for table 1, 2, and 3 in the paper 
foreach Y of numlist 2000/2017{

use "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta", clear


rename defendant_shnro shnro 

local k=`Y'-1

* pull the information one year before the event 
merge m:1 shnro using "${data}fleed_kokonais_`k'.dta", keepusing (sukup ptoim1 tyotu svatva toimiala ammattikoodi tyokk ututku ika tyrtu svatvp) 
keep if _merge==3
drop _merge 


foreach X in sukup ptoim1 tyotu svatva toimiala ammattikoodi tyokk ututku ika tyrtu svatvp{
rename `X' defendant_lag_`X'	
}


rename shnro defendant_shnro 


rename victim_shnro shnro 

merge m:1 shnro  using "${data}fleed_kokonais_`k'.dta", keepusing (sukup ptoim1 tyotu svatva toimiala ammattikoodi tyokk ututku ika tyrtu svatvp) 
keep if _merge==3
drop _merge 


foreach X in sukup ptoim1 tyotu svatva toimiala ammattikoodi tyokk ututku ika tyrtu svatvp{
rename `X' victim_lag_`X'	
}


rename shnro victim_shnro

save "W:\workplace\data\DV_workplace_`Y'_lag.dta", replace

}



foreach Y of numlist 2018{

use "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta", clear

rename defendant_shnro shnro 

gen vuosi=`Y'-1

* pull the information one year before the event 

* merge with this year folk data
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tulo_11a\folk_20112019_tua_tulo21tot_1.dta", keepusing(shnro tyotu tyrtu) 
keep if _merge==3
drop _merge

* merge with this year folk data
merge m:1 shnro vuosi using "D:\ready-made\FOLK_perus_11a\folk_20112020_tua_perus21tot_1.dta", keepusing(shnro ptoim1 ammattikoodi_k ika sukup) 
keep if _merge==3
drop _merge

* merge with this year folk data
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tutk_11a\folk_20112019_tua_tutk21tot_1.dta", keepusing(shnro koulk) 
keep if _merge==3
drop _merge
rename koulk ututku


* merge with this year folk data
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tkt_11a\folk_20112018_tua_tkt21tot_1", keepusing(shnro ammattikoodi) 
keep if _merge==3
drop _merge


foreach X in sukup ptoim1 tyotu ammattikoodi ammattikoodi_k ututku ika tyrtu{
rename `X' defendant_lag_`X'	
}



rename shnro defendant_shnro 


rename victim_shnro shnro 

* merge with this year folk data
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tulo_11a\folk_20112019_tua_tulo21tot_1.dta", keepusing(shnro tyotu tyrtu) 
keep if _merge==3
drop _merge

* merge with this year folk data
merge m:1 shnro vuosi using "D:\ready-made\FOLK_perus_11a\folk_20112020_tua_perus21tot_1.dta", keepusing(shnro ptoim1 ammattikoodi_k ika sukup) 
drop if _merge==2 
drop _merge

* merge with this year folk data
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tutk_11a\folk_20112019_tua_tutk21tot_1.dta", keepusing(shnro koulk)
keep if _merge==3
drop _merge
rename koulk ututku

* merge with this year folk data
merge m:1 shnro vuosi using "D:\ready-made\FOLK_tkt_11a\folk_20112018_tua_tkt21tot_1", keepusing(shnro ammattikoodi) 
keep if _merge==3
drop _merge



foreach X in sukup ptoim1 tyotu ammattikoodi ammattikoodi_k ututku ika tyrtu{
rename `X' victim_lag_`X'	
}



rename shnro victim_shnro

save "W:\workplace\data\DV_workplace_`Y'_lag.dta", replace

}





* prepare data for table 2 
// defendants
use "W:\workplace\data\DV_workplace_2000_lag.dta", clear
foreach Y of numlist 2001/2018{
    append using "W:\workplace\data\DV_workplace_`Y'_lag.dta"
} 


rename defendant_shnro shnro
duplicates report shnro year_event
duplicates drop shnro year_event, force 


gen year=year_event-1  
* merge with crime records for each person
merge m:1 shnro year using  "${dataout}crime_records_year.dta"
drop if _merge==2
drop _merge 
replace record=0 if record==.
replace cum_record=0 if cum_record==.
gen share_record=cum_record>0


foreach X in sukup ptoim1 tyotu svatva toimiala ammattikoodi tyokk ututku ika tyrtu svatvp{
rename  defendant_lag_`X'	`X'
}


	
	*Employment
	destring ptoim1, replace
	gen employed=ptoim1==11 & defendant_sykstun_lag!="."

	*Education variables
	g educ=ututku 
	destring educ, replace
	gen level=substr(ututku, 1,1)
	destring level, replace
	replace level=0 if level==. 
	
	
	*** dummy variable for education 
	gen college=level>=6
	gen high_school=leve>=3 & level<=5
	gen ho=level<3

	

	* Earnings
	replace tyotu = 0 if missing(tyotu)
	replace tyrtu = 0 if missing(tyrtu)
	


	* Sum labor and enrepreneur earnings 
	gen allEarnings =  tyotu + tyrtu
	
	
	gen age=ika
	gen gender=(sukup=="1") 
	
	*Deflate the earnings 
	fmerge m:1 year using  "${dataout}\cpi"
	keep if _merge == 3 
	drop _merge
	

	gen realAllEarn = allEarnings/cpi

	
	drop cpi svatva 



	*Occupation codes
	gen occ=real(substr(ammattikoodi, 1,1))
	gen manager=(occ==1)
	
	gen defendant =1 
	
	gen mm=(suspect_sex=="1" & plaintiff_sex=="1")
	gen mf=(suspect_sex=="1" & plaintiff_sex=="2")
	
	save "W:\workplace\data\DV_workplace_defendant_2000_2018_lag.dta", replace 
	
	
//victim 
use "W:\workplace\data\DV_workplace_2000_lag.dta", clear
foreach Y of numlist 2001/2018{
    append using "W:\workplace\data\DV_workplace_`Y'_lag.dta"
} 

// victims
rename victim_shnro shnro
duplicates report shnro year_event
duplicates drop shnro year_event, force 


gen year=year_event-1  
* merge with crime records for each person
merge m:1 shnro year using  "${dataout}crime_records_year.dta"
drop if _merge==2
drop _merge 
replace record=0 if record==.
replace cum_record=0 if cum_record==.
gen share_record=cum_record>0



foreach X in sukup ptoim1 tyotu svatva toimiala ammattikoodi tyokk ututku ika tyrtu svatvp{
rename  victim_lag_`X'	`X'
}


	
	*Employment
	destring ptoim1, replace
	gen employed=ptoim1==11 & victim_sykstun_lag!="."

	*Education variables
	g educ=ututku 
	destring educ, replace
	gen level=substr(ututku,1,1)
	destring level, replace
	replace level=0 if level==. 
	
	
	*** dummy variable for education 
	gen college=level>=6
	gen high_school=leve>=3 & level<=5
	gen ho=level<3

	

	* Earnings
	replace tyotu = 0 if missing(tyotu)
	replace tyrtu = 0 if missing(tyrtu)
	


	* Sum labor and enrepreneur earnings 
	gen allEarnings =  tyotu + tyrtu
	gen defendant_allEarnings=defendant_tyotu + defendant_tyrtu
 
	
	
	gen age=ika
	gen defendant_age=defendant_ika
	gen gender=(sukup=="1") 
	
	*Deflate the earnings 
	fmerge m:1 year using  "${dataout}\cpi"
	keep if _merge == 3 
	drop _merge
	

	gen realAllEarn = allEarnings/cpi
	gen defendant_realAllEarn = defendant_allEarnings/cpi

	
	drop cpi svatva 



	*Occupation codes
	gen occ=real(substr(ammattikoodi, 1,1))
	gen manager=(occ==1)
	
	gen victim=1 
	gen mm=(suspect_sex=="1" & plaintiff_sex=="1")
	gen mf=(suspect_sex=="1" & plaintiff_sex=="2")
	
save "W:\workplace\data\DV_workplace_victim_2000_2018_lag.dta", replace 

*/ 





// Table 1: crime types 
// workplace violence 
use "W:\workplace\data\police_DV_workplace_defendant_2000.dta", clear
foreach Y of numlist 2001/2017{
append using "W:\workplace\data\police_DV_workplace_defendant_`Y'.dta"
}


duplicates drop defendant_shnro victim_shnro year_event case_id, force 

* definition of new wp crimes 
keep if defendant_sykstun_lag== victim_sykstun_lag & defendant_sykstun_lag!=""

tab crimecode6


gen Crimes=""
gen count=1 
bysort crimecode6: egen count_crime=total(count)


replace Crimes="Assault" if crimecode6=="210501"
replace Crimes="Petty assault" if crimecode6=="210701"
replace Crimes="Negligent bodily injury" if crimecode6=="211001"
replace Crimes="Menace" if crimecode6=="250701"

replace Crimes="Others" if Crimes==""

gen Crime_mm= Crimes if suspect_sex=="1" & plaintiff_sex=="1"
gen Crime_mf= Crimes if  suspect_sex=="1" & plaintiff_sex=="2"

tabout Crimes using  "${results}\Table1a_crime_type_workplace.tex", replace c(freq col) f(0  1) /// 
clab (Crimes Freq Percent) style (tex) bt font(bold) 
 
tabout Crime_mm using  "${results}\Table1a_crime_type_mm_workplace.tex", replace c(freq col) f(0  1) /// 
clab (Crime_men Freq Percent) style (tex) bt font(bold) 

tabout Crime_mf using  "${results}\Table1a_crime_type_mf_workplace.tex", replace c(freq col) f(0  1) /// 
clab (Crime_women Freq Percent) style (tex) bt font(bold) 


// Non workplace violence 

use "W:\workplace\data\altcounter_sample.dta", clear 
keep if crime_type== 1 
drop if wp_crime==1 
tab crimecode6


gen Crimes=""
gen count=1 
bysort crimecode6: egen count_crime=total(count)


replace Crimes="Assault" if crimecode6=="210501"
replace Crimes="Petty assault" if crimecode6=="210701"
replace Crimes="Negligent bodily injury" if crimecode6=="211001"
replace Crimes="Menace" if crimecode6=="250701"

gen Crime_mm= Crimes if suspect_sex=="1" & plaintiff_sex=="1"
gen Crime_mf= Crimes if  suspect_sex=="1" & plaintiff_sex=="2"

tabout Crimes using  "${results}\Table1b_crime_type_nonworkplace.tex", replace c(freq col) f(0  1) /// 
clab (Crimes Freq Percent) style (tex) bt font(bold) 
 
tabout Crime_mm using  "${results}\Table1b_crime_type_mm_nonworkplace.tex", replace c(freq col) f(0  1) /// 
clab (Crime_men Freq Percent) style (tex) bt font(bold) 

tabout Crime_mf using  "${results}\Table1b_crime_type_mf_nonworkplace.tex", replace c(freq col) f(0  1) /// 
clab (Crime_women Freq Percent) style (tex) bt font(bold) 


// Table 2a: workplace violence 

// tenure at current employer 
forvalues Y=1999/2017{
use "W:\workplace\data\DV_workplace_defendant_2000_2018_lag.dta", replace 
append using "W:\workplace\data\DV_workplace_victim_2000_2018_lag.dta"

keep if defendant_sykstun_lag== victim_sykstun_lag & victim_sykstun_lag!=""

gen sykstun_current=defendant_sykstun_lag 


keep if year==`Y'

forvalues i=1/10{
    local m`i'=`Y'-`i' 
    * merge with past 5 year's characteristics for matching
merge m:1 shnro using "D:\ready-made\FLEED_TOTAL\2016\fleed_kokonais_`m`i''.dta", keepusing(sykstun) keep(master match) nogen 
rename sykstun sykstun`i'
}

gen tenure_current=1 

forvalues i=1/10{
    replace tenure_current=`i'+1 if sykstun_current==sykstun`i'
}


tempfile cat_`Y'
save `cat_`Y'', replace 
}

clear
forvalues Y=1999/2017{
    append using `cat_`Y''
}
	
save "W:\workplace\data\table2a_data", replace 

use "W:\workplace\data\table2a_data", replace 

//overall tenure 
merge m:1 shnro year using "W:\workplace\data\ind_workers_tenure_overall", keep(master match) keepusing(tenure_overall) nogen 

replace tenure_overall=0 if tenure_overall==. 


preserve 

replace cum_record=0 if cum_record==. 

gen professionals=(occ==2 | occ==3)
gen clerical_service=(occ==4 | occ==5)


eststo wp_perp: estpost sum gender age  college high_school ho employed realAllEarn tenure_current tenure_overall manager professionals clerical_service cum_record if defendant==1 
eststo wp_victim: estpost sum gender age college high_school ho employed realAllEarn tenure_current tenure_overall manager professionals clerical_service cum_record if victim==1 

eststo wp_perp_mf: estpost sum gender age  college high_school ho employed realAllEarn tenure_current tenure_overall manager professionals clerical_service cum_record if defendant==1 & mf==1 
eststo wp_victim_mf: estpost sum gender age  college high_school ho employed realAllEarn tenure_current tenure_overall manager professionals clerical_service cum_record if victim==1 & mf==1 

eststo wp_perp_mm: estpost sum gender age  college high_school ho employed realAllEarn tenure_current tenure_overall manager professionals clerical_service cum_record if defendant==1 & mm==1 
eststo wp_victim_mm: estpost sum gender age  college high_school ho employed realAllEarn tenure_current tenure_overall manager professionals clerical_service cum_record if victim==1 & mm==1 



esttab wp_perp wp_victim wp_perp_mf wp_victim_mf wp_perp_mm wp_victim_mm   using "${results}\Table2a_victim_perp_wp.tex", replace cell("mean (pattern (1 1 1 1 1 1) fmt(2))") modelwidth (10) mtitle ("Workplace violence victims" "Male-female victims" "Male-male victims" "Workplace violence defendants" "Male-female defendants" "Male-male defendants") nonumber  coeflabel (age "Age" gender "Gender" college "Share college" high_school "Share high school" ho "Share dropouts" employed "Employment" realAllEarn "Earnings" tenure_current "Tenure at current employer" tenure_overall "Overall tenure" cum_record "Prior crimes" manager "Share manager" professionals "Share professionals" clerical_service "Share clerical and service workers") title (Table 1. Panel A) nonotes addn (Among all the female-on-male crimes, 43\% cases are simultaneously male-on-female crimes. Among all the male-on-female crimes, 9.8\% are are both defendants and victims. Among all the male-on-male crimes, 18\% are both defendants and victims. Data from 2000 to 2018)
restore 




// Table 2b: non workplace violence 

use "W:\workplace\data\altcounter_sample.dta", clear 
keep if crime_type== 1 
drop if wp_crime==1 


drop shnro 
rename re_shnro shnro 

gen mf=(plaintiff_sex=="2")
gen mm=(plaintiff_sex=="1")

keep shnro suspect* year mm mf 
rename suspect_* * 

gen defendant=1 

tempfile cat
save `cat', replace 


use "W:\workplace\data\altcounter_sample.dta", clear 
keep if crime_type== 1 
drop if wp_crime==1 

gen mf=(plaintiff_sex=="2")
gen mm=(plaintiff_sex=="1")


drop shnro 
rename ao_shnro shnro 
rename plaintiff_sex victim_sex 

keep shnro victim* year mm mf 
rename victim_* * 

gen victim=1 
append using `cat'

//overall tenure 
merge m:1 shnro year using "W:\workplace\data\ind_workers_tenure_overall", keep(master match) keepusing(tenure_overall) nogen 

rename ika age 

gen gender=(sex=="1")

gen occ=real(substr(ammattikoodi, 1,1))

replace tenure_overall=0 if tenure_overall==. 

preserve 

replace cum_record=0 if cum_record==. 


gen professionals=(occ==2 | occ==3)
gen clerical_service=(occ==4 | occ==5)


eststo wp_perp: estpost sum gender age  college high_school ho employed realAllEarn tenure_overall manager professionals clerical_service cum_record if defendant==1 
eststo wp_victim: estpost sum gender age college high_school ho employed realAllEarn tenure_overall manager professionals clerical_service cum_record if victim==1 

eststo wp_perp_mf: estpost sum gender age  college high_school ho employed realAllEarn tenure_overall manager professionals clerical_service cum_record if defendant==1 & mf==1 
eststo wp_victim_mf: estpost sum gender age  college high_school ho employed realAllEarn tenure_overall manager professionals clerical_service cum_record if victim==1 & mf==1 

eststo wp_perp_mm: estpost sum gender age  college high_school ho employed realAllEarn tenure_overall manager professionals clerical_service cum_record if defendant==1 & mm==1 
eststo wp_victim_mm: estpost sum gender age  college high_school ho employed realAllEarn tenure_overall manager professionals clerical_service cum_record if victim==1 & mm==1 



esttab wp_perp wp_victim wp_perp_mf wp_victim_mf wp_perp_mm wp_victim_mm  using "${results}\Table2b_victim_perp_nowp.tex", replace cell("mean (pattern (1 1 1 1 1 1) fmt(2))") modelwidth (10) mtitle ("Workplace violence victims" "Male-female victims" "Male-male victims" "Workplace violence defendants" "Male-female defendants" "Male-male defendants") nonumber  coeflabel (age "Age" gender "Gender" college "Share college" high_school "Share high school" ho "Share dropouts" employed "Employment" realAllEarn "Earnings" tenure_overall "Overall tenure" cum_record "Prior crimes" manager "Share manager" professionals "Share professionals" clerical_service "Share clerical and service workers") title (Table 2. Panel B) nonotes 
restore 



// Table 3 

use "W:\workplace\data\police_DV_workplace_victim_2000.dta", clear
foreach Y of numlist 2001/2018{
    append using "W:\workplace\data\police_DV_workplace_victim_`Y'.dta"
} 

keep if defendant_sykstun_lag==victim_sykstun_lag & defendant_sykstun_lag!=""

gen sykstun=victim_sykstun_lag 

// summary statistics for firm characteristics by crime types 
gen mm=(suspect_sex=="1" & plaintiff_sex=="1") 
gen mf=(suspect_sex=="1" & plaintiff_sex=="2")


collapse (max) mm (max) mf wp_crime, by(sykstun year_event)

* one year before the event 
gen year=year_event-1 
merge m:1 sykstun year using "${dataout}plant_char_allyears"
drop _merge 

replace wp_crime=0 if wp_crime==. 
drop if year<1999




preserve 
drop if employee==1
* topcoded firm size 
replace employee=5000 if employee>5000
replace female_employee=3000 if female_employee>3000
gen share_newhire=newhire/employee
gen share_female_newhire=female_newhire/employee
gen share_switch=-switch/employee
gen share_switch_female=-switch_female/employee

gen pay_gap=male_earnings-female_earnings
gen share_female_manager=female_manager/(male_manager+female_manager)


sum employee if wp_crime==1, d
gen median_employee_wp=r(p50)

sum employee if mf==1 & wp_crime==1, d
gen median_employee_mf=r(p50)

sum employee if mm==1 & wp_crime==1, d
gen median_employee_mm=r(p50)

sum employee if wp_crime==0, d
gen median_employee_nowp=r(p50)


sum pay_gap if wp_crime==1, d
gen median_pay_gap_wp=r(p50)

sum pay_gap if mf==1 & wp_crime==1, d
gen median_pay_gap_mf=r(p50)

sum pay_gap if mm==1 & wp_crime==1, d
gen median_pay_gap_mm=r(p50)

sum pay_gap if wp_crime==0, d
gen median_pay_gap_nowp=r(p50)


eststo violent: estpost sum employee median_employee_wp earnings college high_school ho age  tenure share_female share_switch share_switch_female share_newhire share_female_newhire male_earnings female_earnings pay_gap median_pay_gap_wp share_female_manager if wp_crime==1

eststo mf: estpost sum employee  median_employee_mf earnings college high_school ho age  tenure share_female share_switch share_switch_female share_newhire share_female_newhire  male_earnings female_earnings pay_gap  median_pay_gap_mf share_female_manager if mf==1 & wp_crime==1

eststo mm: estpost sum employee  median_employee_mm earnings college high_school ho age  tenure share_female share_switch share_switch_female share_newhire share_female_newhire  male_earnings female_earnings pay_gap median_pay_gap_mm share_female_manager if mm==1 & wp_crime==1

eststo other: estpost sum employee  median_employee_nowp earnings college high_school ho age  tenure share_female share_switch share_switch_female share_newhire share_female_newhire male_earnings female_earnings median_pay_gap_nowp pay_gap share_female_manager if wp_crime==0

esttab violent mf mm other using "${results}\Table3_firm.tex", replace cell("mean (pattern (1 1 1 1) fmt(2))") modelwidth (10) mtitle ("Violent firms" "Male-female crimes" "Male-male crimes" "Other firms") nonumber  coeflabel (employee "Average firm size"  earnings "Average wages" college "Share college" high_school "Share high school" ho "Share dropouts" age "Age"  tenure "Average tenure" share_female "Share of women" share_switch "Turnover rate" share_switch_female "Female turnover rate" share_newhire "Share of new hires" share_female_newhire "Share of female new hires" male_earnings "Male earnings" female_earnings "Female earnings" pay_gap "Average gender pay gap (male-female)" share_female_manager "Share female managers" ) title (Table 2. Summary Statistics: Firms) nonotes addn (Notes: Data from 2000 to 2018)
restore 


